package com.example.multidatasource.dao.impl;

import com.example.multidatasource.dao.RougeDao;
import com.example.multidatasource.service.dto.BrandInfo;
import com.example.multidatasource.service.dto.RougeInfo;
import com.example.multidatasource.service.dto.ShopInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author : zhayh
 * @date : 2021-3-31 20:21
 * @description : 口红商品的数据库处理
 */

@Repository
public class RougeDaoImpl implements RougeDao {
    @Resource
    private JdbcTemplate jdbcTemplateOne;

    @Resource
    private JdbcTemplate jdbcTemplateTwo;

    @Override
    public List<BrandInfo> getAllBrandInfo() {
        return jdbcTemplateOne.query("select * from brand_info",
                new BeanPropertyRowMapper<>(BrandInfo.class));
    }

    @Override
    public RougeInfo selectById(Long id) {
        String sql = "select a.*,b.brand_name,b.brand_founder,b.brand_description,b.brand_country," +
                "c.shop_name,c.shop_fans_count,c.shop_time " +
                " from rouge_info a " +
                "left join brand_info b on b.id=a.brand_id " +
                "left join shop_info c on c.id=a.shop_id " +
                " where a.id=?";
        return jdbcTemplateOne.queryForObject(sql, (rs, rowNum) -> {
            BrandInfo brandInfo = BrandInfo.builder()
                    .id(rs.getLong("a.brand_id"))
                    .brandName(rs.getString("b.brand_name"))
                    .brandFounder(rs.getString("b.brand_founder"))
                    .brandCountry(rs.getString("b.brand_country"))
                    .brandDescription(rs.getString("b.brand_description"))
                    .build();
            ShopInfo shopInfo = ShopInfo.builder()
                    .id(rs.getLong("a.shop_id"))
                    .shopName(rs.getString("c.shop_name"))
                    .shopFansCount(rs.getString("c.shop_fans_count"))
                    .shopTime(rs.getString("c.shop_time"))
                    .build();
            return RougeInfo.builder().id(id)
                    .rougeName(rs.getString("a.rouge_name"))
                    .rougePrice(rs.getFloat("a.rouge_price"))
                    .shopInfo(shopInfo)
                    .brandInfo(brandInfo)
                    .build();
        }, id);
    }

    @Override
    public RougeInfo insert(RougeInfo rougeInfo) {
        Long brandId = rougeInfo.getBrandInfo().getId();
        Long shopId = rougeInfo.getShopInfo().getId();
        String sql = "insert into rouge_info(rouge_name,rouge_price,shop_id,brand_id) values(?,?,?,?)";
        KeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplateOne.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, rougeInfo.getRougeName());
            ps.setFloat(2, rougeInfo.getRougePrice());
            ps.setLong(3, shopId);
            ps.setLong(4, brandId);
            return ps;
        }, holder);
        rougeInfo.setId(Objects.requireNonNull(holder.getKey()).longValue());
        return rougeInfo;
    }

    @Override
    public RougeInfo updatePrice(Long id, Float price) {
        String sql = "update rouge_info set rouge_price = ? where id = ?";
        if (jdbcTemplateOne.update(sql, price, id) > 0) {
            sql = "select a.*,b.brand_name,b.brand_founder,b.brand_description,b.brand_country," +
                    "c.shop_name,c.shop_fans_count,c.shop_time " +
                    " from rouge_info a " +
                    "left join brand_info b on b.id=a.brand_id " +
                    "left join shop_info c on c.id=a.shop_id " +
                    " where a.id=?";
            return jdbcTemplateOne.queryForObject(sql, (rs, rowNum) -> {
                BrandInfo brandInfo = BrandInfo.builder()
                        .id(rs.getLong("a.brand_id"))
                        .brandName(rs.getString("b.brand_name"))
                        .brandFounder(rs.getString("b.brand_founder"))
                        .brandCountry(rs.getString("b.brand_country"))
                        .brandDescription(rs.getString("b.brand_description"))
                        .build();
                ShopInfo shopInfo = ShopInfo.builder()
                        .id(rs.getLong("a.shop_id"))
                        .shopName(rs.getString("c.shop_name"))
                        .shopFansCount(rs.getString("c.shop_fans_count"))
                        .shopTime(rs.getString("c.shop_time"))
                        .build();
                return RougeInfo.builder().id(id)
                        .rougeName(rs.getString("a.rouge_name"))
                        .rougePrice(rs.getFloat("a.rouge_price"))
                        .shopInfo(shopInfo)
                        .brandInfo(brandInfo)
                        .build();
            }, id);
        }
        return null;
    }

    @Override
    public int delete(Long id) {
        return jdbcTemplateTwo.update("delete from rouge_info where id=?", id);
    }

    @Override
    public Page<RougeInfo> selectByPage(Integer pageIndex, Integer pageSize) {
        Integer start = pageIndex * pageSize;
        String sql = "select a.*,b.brand_name,b.brand_founder,b.brand_description,b.brand_country," +
                "c.shop_name,c.shop_fans_count,c.shop_time " +
                " from rouge_info a " +
                "left join brand_info b on b.id=a.brand_id " +
                "left join shop_info c on c.id=a.shop_id ";
        String pageSql = "select * from (" + sql + ") tb limit ?, ?";
        List<RougeInfo> rougeInfos = jdbcTemplateTwo.query(pageSql, (rs, rowNum) -> {
            // 使用RowMapper解析数据对象
            BrandInfo brandInfo = BrandInfo.builder()
                    .id(rs.getLong("brand_id"))
                    .brandName(rs.getString("brand_name"))
                    .brandFounder(rs.getString("brand_founder"))
                    .brandCountry(rs.getString("brand_country"))
                    .brandDescription(rs.getString("brand_description"))
                    .build();
            ShopInfo shopInfo = ShopInfo.builder()
                    .id(rs.getLong("shop_id"))
                    .shopName(rs.getString("shop_name"))
                    .shopFansCount(rs.getString("shop_fans_count"))
                    .shopTime(rs.getString("shop_time"))
                    .build();
            return RougeInfo.builder()
                    .id(rs.getLong("id"))
                    .rougeName(rs.getString("rouge_name"))
                    .rougePrice(rs.getFloat("rouge_price"))
                    .shopInfo(shopInfo)
                    .brandInfo(brandInfo)
                    .build();
        }, start, pageSize);
        // 查询口红总数的sql
        String countSql = "select count(1) totalCount from (" + sql + ") tb";
        Long totalCount = jdbcTemplateTwo.queryForObject(countSql, Long.class);
        //使用PageImpl构造分页数据对象返回
        return new PageImpl(rougeInfos, PageRequest.of(pageIndex, pageSize), totalCount);
    }

    @Override
    public Long getTotalCount() {
        String sql = "select count(1) totalCount from rouge_info";
        Map<String, Object> countMap = jdbcTemplateTwo.queryForMap(sql);
        return (Long) countMap.get("totalCount");
    }

    public int[] batchInsert(List<BrandInfo> brandInfos) {
        String sql = "insert into brand_info(brand_name, brand_founder, brand_description, brand_country) " +
                "values(?,?,?,?)";
        return jdbcTemplateTwo.batchUpdate(sql,
                new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setString(1, brandInfos.get(i).getBrandName());
                        ps.setString(2, brandInfos.get(i).getBrandFounder());
                        ps.setString(3, brandInfos.get(i).getBrandDescription());
                        ps.setString(4, brandInfos.get(i).getBrandCountry());
                    }

                    @Override
                    public int getBatchSize() {
                        return brandInfos.size();
                    }
                });
    }
}
